﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using bdnt_pets_system_Entity;
using System.Data;

namespace bdnt_pets_system_Bo.DataManager
{
    public class FailureRecordManager
    {
        //AccessHelperSQL accessHelper = AccessHelperSQL.GetInstance();
        AccessHelper accessHelper = AccessHelper.GetInstance();

        /// <summary>
        /// 
        /// </summary>
        /// <param name="cuObj"></param>
        /// <param name="fauilers"></param>
        /// <returns>-1没有此车辆,-2已经存在同车同部件同位置的故障</returns>
        public bool SaveObj(Tbl_FailureRecord cuObj, List<SimpleObject<int, int>> fauilers)
        {
            List<string> sqls = new List<string>();
            foreach (var item in fauilers)
            {
                sqls.Add(string.Format(@"INSERT INTO Tbl_FailureRecord
           (RecordTime,RecordUserId,ProductionNo,VINNo,PartNo,PositionNo,FailureId
            ,RepairStatus,RepairResult,RepsPersonId,RepsData,TrackingFlag,FailureLevel
            ,RecordShift,CheckerUserId,CheckTime,FCount) values({0},{1},'{2}','{3}','{4}','{5}',{6},
                    {7},'{8}',{9},{10},{11},{12},
                    {13},{14},{15},{16})", (cuObj.RecordTime.HasValue && cuObj.RecordTime.Value.Year != 1900 ?
             "to_date('" + cuObj.RecordTime.Value.ToString("yyyy-MM-dd") + "','yyyy-MM-dd')" : "NULL"),
                   cuObj.RecordUserId, cuObj.ProductionNo, cuObj.VINNo, cuObj.PartNo,
                  cuObj.PositionNo,
                  item.Item1,
                    //cuObj.FailureId, 
                       cuObj.RepairStatus ? 1 : 0,
                    //cuObj.RepairStatus,
                  cuObj.RepairResult, cuObj.RepsPersonId,
                  (cuObj.RepsData.HasValue && cuObj.RepsData.Value.Year != 1900 ?
              "to_date('" + cuObj.RepsData.Value.ToString("yyyy-MM-dd") + "','yyyy-MM-dd')" : "NULL"),
              cuObj.TrackingFlag ? 1 : 0,
                 item.Item2,
                    //cuObj.FailureLevel, 
                 cuObj.RecordShift
                  , cuObj.CheckerUserId,
                     (cuObj.CheckTime.HasValue && cuObj.CheckTime.Value.Year != 1900 ?
               "to_date('" + cuObj.CheckTime.Value.ToString("yyyy-MM-dd") + "','yyyy-MM-dd')" : "NULL"), cuObj.FCount));
            }
            //sqlBuilder.Insert(0, sql);
            return accessHelper.ExecuteNonQuery(sqls) > 0;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="cuObj"></param>
        /// <param name="fauilers"></param>
        /// <returns>-1没有此车辆,-2已经存在同车同部件同位置的故障</returns>
        public Int32 NewAndSaveFailureRecord(Tbl_FailureRecord cuObj, List<SimpleObject<int, int>> fauilers)
        {
            if (cuObj.Id == 0)
            {
                VehicleManager v = new VehicleManager();
                var cuo = v.GetVehicleByProNoOrVinNo(cuObj.ProductionNo, cuObj.VINNo);
                if (cuo == null)
                    return -1;//没有此车辆
                string fis = string.Join(",", fauilers.Select(p => p.Item1));

                string sql = string.Format(@"select distinct r.failureid from tbl_failurerecord r 
                where (r.vinno='{0}' or r.productionno='{1}') and r.partno='{2}' and r.positionno='{3}' and r.failureid in ({4})"
                , cuObj.VINNo, cuObj.ProductionNo, cuObj.PartNo, cuObj.PositionNo, fis);
                var dt = accessHelper.ExcuteDataTable(sql);
                if (dt != null && dt.Rows.Count > 0)
                {
                    foreach (DataRow item in dt.Rows)
                    {
                        var cuIt = fauilers.FirstOrDefault(p => p.Item1 == Int32.Parse(item["failureid"].ToString()));
                        fauilers.Remove(cuIt);
                    }

                    if (fauilers.Count == 0)
                        return -2;  //已经存在同车同部件同位置的故障
                    //var cur = Int32.Parse(dt.ToString());
                    //if (cur > 0)
                    //    return -2; //已经存在同车同部件同位置的故障
                }
            }

            return SaveObj(cuObj, fauilers) ? 1 : 0;
        }

        public bool UpdateObj(Tbl_FailureRecord cuObj)
        {
            string sql = string.Format(@"Update Tbl_FailureRecord set FCount={0},RepairStatus={1},RepairResult='{2}'
                            Where Id={3}", cuObj.FCount, cuObj.RepairStatus ? 1 : 0, cuObj.RepairResult, cuObj.Id);
            return accessHelper.ExcultData(sql);
        }

        public bool DeleteObj(Int32 objId)
        {
            string sql = string.Format(@"delete from Tbl_FailureRecord Where Id={0}", objId);
            return accessHelper.ExcultData(sql);
        }

        public List<Tbl_FailureRecord> GetObjListByCondition(string vINNO, string partNo, string partName, string failureName, int stationId,
           int checkerUserId, DateTime? sCheckTime, DateTime? eCheckTime, int isFx, int isZz,
            int pageSize, int currentPage, DateTime? sRecordTime, DateTime? eRecordTime, string productionNo, int recordUserId)
        {
            //StringBuilder sql = new StringBuilder("select row_number() over(order by Id desc) recordId,* from Tbl_FailureInfo where 1=1");
            StringBuilder sql = new StringBuilder(@"select row_number() over(order by fr.Id desc) recordId,
           fr.*,pt.PartName,pt.en_PartName,tf.FailureName,
           tf.en_FailureName,emp.EmpName CheckerUserName,reEmp.EmpName RecordUserName 
             from Tbl_FailureRecord fr 
            left join Tbl_FailureInfo tf on fr.FailureId=tf.Id
            Left join Tbl_Part pt on pt.PartNo=tf.PartNo
            left join Tbl_Employee emp on emp.id=fr.checkerUserId 
            left join Tbl_Employee reEmp on reEmp.id=fr.RecordUserId
            left join Tbl_StationMemebers sm on sm.EmpId=emp.Id
            where 1=1");

            if (!string.IsNullOrEmpty(vINNO))
                sql = sql.AppendFormat(@" and VINNO like '%{0}%'", vINNO);
            if (!string.IsNullOrEmpty(partNo))
                sql = sql.AppendFormat(@" and fr.partNo='{0}'", partNo);
            if (!string.IsNullOrEmpty(partName))
                sql = sql.AppendFormat(@" and pt.en_PartName like '%{0}%'", partName);
            if (!string.IsNullOrEmpty(failureName))
                sql = sql.AppendFormat(@" and tf.en_FailureName like '%{0}%'", failureName);
            if (stationId > 0)
                sql = sql.AppendFormat(@" and sm.StationId ={0}", stationId);
            if (checkerUserId > 0)
                sql = sql.AppendFormat(@" and fr.checkerUserId ={0}", checkerUserId);
            if (sCheckTime.HasValue)
                sql = sql.AppendFormat(@" and ROUND(TO_NUMBER(to_date('{0}','yyyy-mm-dd') - fr.CheckTime))>=0", sCheckTime.Value.ToString("yyyy-MM-dd"));
            if (eCheckTime.HasValue)
                sql = sql.AppendFormat(@" and ROUND(TO_NUMBER(to_date('{0}','yyyy-mm-dd') - fr.CheckTime))<=0", eCheckTime.Value.ToString("yyyy-MM-dd"));
            if (isFx > -1)
                sql = sql.AppendFormat(@" and RepairStatus={0}", isFx);
            if (isZz > -1)
                sql = sql.AppendFormat(@" and TrackingFlag={0}", isZz);
            if (sRecordTime.HasValue)
                sql = sql.AppendFormat(@" and ROUND(TO_NUMBER(to_date('{0}','yyyy-mm-dd') - fr.RecordTime))>=0", sRecordTime.Value.ToString("yyyy-MM-dd"));
            if (eRecordTime.HasValue)
                sql = sql.AppendFormat(@" and ROUND(TO_NUMBER(to_date('{0}','yyyy-mm-dd') - fr.RecordTime))<=0", eRecordTime.Value.ToString("yyyy-MM-dd"));
            if (!string.IsNullOrEmpty(productionNo))
                sql = sql.AppendFormat(@" and fr.ProductionNo like '%{0}%'", productionNo);
            if (recordUserId > 0)
                sql = sql.AppendFormat(@" and fr.RecordUserId ={0}", recordUserId);


            int startNum = pageSize * (currentPage > 0 ? currentPage - 1 : 0);
            int endNum = pageSize * currentPage;
            sql.Insert(0, @"select re.*,(select DictName from Tbl_DictInfo where PId=15 and DictValue=RepairResult and rownum=1) RepairResultText
            ,(select en_DictName from Tbl_DictInfo where PId=15 and DictValue=RepairResult and rownum=1) en_RepairResultText 
            ,(select DictName from Tbl_DictInfo where PId=8 and DictValue=PositionNo and rownum=1) PositionName
            ,(select en_DictName from Tbl_DictInfo where PId=8 and DictValue=PositionNo and rownum=1) en_PositionName
            from (");
            sql.AppendFormat(@") re where re.recordId>{0} and re.recordId<={1}", startNum, endNum);
            var dt = accessHelper.ExcuteDataTable(sql.ToString());
            List<Tbl_FailureRecord> returnlist = new List<Tbl_FailureRecord>();
            foreach (DataRow dr in dt.Rows)
            {
                returnlist.Add(new Tbl_FailureRecord
                {
                    Id = dr["Id"] == DBNull.Value ? 0 : Int32.Parse(dr["Id"].ToString()),
                    VINNo = dr["VINNo"] == DBNull.Value ? "" : dr["VINNo"].ToString(),
                    PartName = dr["PartName"] == DBNull.Value ? "" : dr["PartName"].ToString(),
                    en_PartName = dr["en_PartName"] == DBNull.Value ? "" : dr["en_PartName"].ToString(),
                    PartNo = dr["PartNo"] == DBNull.Value ? "" : dr["PartNo"].ToString(),
                    FailureId = dr["FailureId"] == DBNull.Value ? 0 : Int32.Parse(dr["FailureId"].ToString()),
                    PositionNo = dr["PositionNo"] == DBNull.Value ? "" : dr["PositionNo"].ToString(),
                    FailureName = dr["FailureName"] == DBNull.Value ? "" : dr["FailureName"].ToString(),
                    en_FailureName = dr["en_FailureName"] == DBNull.Value ? "" : dr["en_FailureName"].ToString(),
                    CheckTime = dr["CheckTime"] == DBNull.Value ? (DateTime?)null : DateTime.Parse(dr["CheckTime"].ToString()),
                    RecordTime = dr["RecordTime"] == DBNull.Value ? (DateTime?)null : DateTime.Parse(dr["RecordTime"].ToString()),
                    CheckerUserName = dr["CheckerUserName"] == DBNull.Value ? "" : dr["CheckerUserName"].ToString(),
                    RepairStatus = dr["RepairStatus"] == DBNull.Value ? false : dr["RepairStatus"].ToString() == "1" ? true : false,
                    RepairResult = dr["RepairResult"] == DBNull.Value ? "" : dr["RepairResult"].ToString(),
                    TrackingFlag = dr["TrackingFlag"] == DBNull.Value ? false : dr["TrackingFlag"].ToString() == "1" ? true : false,
                    RepairResultText = dr["RepairResultText"] == DBNull.Value ? "" : dr["RepairResultText"].ToString(),
                    en_RepairResultText = dr["en_RepairResultText"] == DBNull.Value ? "" : dr["en_RepairResultText"].ToString(),
                    FCount = dr["FCount"] == DBNull.Value ? 1 : Int32.Parse(dr["FCount"].ToString()),
                    PositionName = dr["PositionName"] == DBNull.Value ? "" : dr["PositionName"].ToString(),
                    en_PositionName = dr["en_PositionName"] == DBNull.Value ? "" : dr["en_PositionName"].ToString(),
                    RecordUserId = dr["RecordUserId"] == DBNull.Value ? 1 : Int32.Parse(dr["RecordUserId"].ToString()),
                    RecordUserName = dr["RecordUserName"] == DBNull.Value ? "" : dr["RecordUserName"].ToString()

                });
            }
            return returnlist;
        }

        public int GetObjListByConditionCount(string vINNO, string partNo, string partName, string failureName, int stationId,
           int checkerUserId, DateTime? sCheckTime, DateTime? eCheckTime, int isFx, int isZz, DateTime? sRecordTime, DateTime? eRecordTime, string productionNo,
            int recordUserId)
        {
            //StringBuilder sql = new StringBuilder("select count(0) as allCount from Tbl_FailureInfo where 1=1");
            StringBuilder sql = new StringBuilder(@"select count(fr.VINNO) allCount
             from Tbl_FailureRecord fr 
            left join Tbl_FailureInfo tf on fr.FailureId=tf.Id
             Left join Tbl_Part pt on pt.PartNo=tf.PartNo
            left join Tbl_Employee emp on emp.id=fr.checkerUserId 
            left join Tbl_StationMemebers sm on sm.EmpId=emp.Id
            where 1=1");

            if (!string.IsNullOrEmpty(vINNO))
                sql = sql.AppendFormat(@" and VINNO like '%{0}%'", vINNO);
            if (!string.IsNullOrEmpty(partNo))
                sql = sql.AppendFormat(@" and fr.PartNo='{0}'", partNo);
            if (!string.IsNullOrEmpty(partName))
                sql = sql.AppendFormat(@" and pt.PartName like '%{0}%'", partName);
            if (!string.IsNullOrEmpty(failureName))
                sql = sql.AppendFormat(@" and tf.FailureName like '%{0}%'", failureName);
            if (stationId > 0)
                sql = sql.AppendFormat(@" and sm.StationId ={0}", stationId);
            if (checkerUserId > 0)
                sql = sql.AppendFormat(@" and fr.checkerUserId ={0}", checkerUserId);
            if (sCheckTime.HasValue)
                sql = sql.AppendFormat(@" and ROUND(TO_NUMBER(to_date('{0}','yyyy-mm-dd') - fr.CheckTime))>=0", sCheckTime.Value.ToString("yyyy-MM-dd"));
            if (eCheckTime.HasValue)
                sql = sql.AppendFormat(@" and ROUND(TO_NUMBER(to_date('{0}','yyyy-mm-dd') - fr.CheckTime))<=0", eCheckTime.Value.ToString("yyyy-MM-dd"));
            if (isFx > -1)
                sql = sql.AppendFormat(@" and RepairStatus={0}", isFx);
            if (isZz > -1)
                sql = sql.AppendFormat(@" and TrackingFlag={0}", isZz);
            if (sRecordTime.HasValue)
                sql = sql.AppendFormat(@" and ROUND(TO_NUMBER(to_date('{0}','yyyy-mm-dd') - fr.RecordTime))<=0", sRecordTime.Value.ToString("yyyy-MM-dd"));
            if (eRecordTime.HasValue)
                sql = sql.AppendFormat(@" and ROUND(TO_NUMBER(to_date('{0}','yyyy-mm-dd') - fr.RecordTime))<=0", eRecordTime.Value.ToString("yyyy-MM-dd"));
            if (!string.IsNullOrEmpty(productionNo))
                sql = sql.AppendFormat(@" and fr.ProductionNo like '%{0}%'", productionNo);
            if (recordUserId > 0)
                sql = sql.AppendFormat(@" and fr.RecordUserId ={0}", recordUserId);

            var dt = accessHelper.ExecuteScalar(sql.ToString());
            if (dt != null)
                return Int32.Parse(dt.ToString());
            return 0;
        }
    }
}
